/*
 * 中北大学软件学院版权所有2020-2025
 */
package nuc.lp.uolab.labmember.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import nuc.lp.uolab.labmember.vo.LabMember;
import nuc.lp.uolab.util.DbUtil;

/**
 * LabMemberDao实现类
 * @author liupeng
 * @version 1.0
 */
public class LabMemberDaoImpl implements LabMemberDao{
	
	
	@Override
	public List<LabMember> selectLabMember(String labmembername, String grade, String telephone, String dept,
			String major, String status, String job) {
		Connection connection = DbUtil.getConnection();
		Statement stmt = null;// 非预编译对象
		ResultSet rs = null;
		List<LabMember> members = new ArrayList<LabMember>();
		// 不知道用户集体的查询，所以SQL是动态变化的
		String sql = "SELECT\r\n"
				+ "t_labmember.*\r\n"
				+ "FROM\r\n"
				+ "t_class\r\n"
				+ "INNER JOIN t_major ON t_class.major_id = t_major.major_id\r\n"
				+ "INNER JOIN t_labmember ON t_labmember.cno = t_class.cno\r\n"
				+ "INNER JOIN t_department ON t_major.did = t_department.did\r\n"
				+ "WHERE 1=1";
		// 可变字符串类
		StringBuilder strBuilder = new StringBuilder(sql);
		if (labmembername != null) {
			strBuilder.append(" AND stu_name = '" + labmembername + "'");
		}
		if (grade != null) {
			strBuilder.append(" AND grade = '" + grade + "'");
		}
		if (telephone != null) {
			strBuilder.append(" AND telephone = '" + telephone + "'");
		}
		if (dept != null) {
			strBuilder.append(" AND dname = '" + dept + "'");
		}
		if (major != null) {
			strBuilder.append(" AND major_name = '" + major + "'");
		}
		if (status != null) {
			strBuilder.append(" AND status = '" + status + "'");
		}
		if (job != null) {
			strBuilder.append(" AND remark = '" + job + "'");
		}
		sql = strBuilder.toString();
		System.out.println(sql);

		try {
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sql);
			// 把rSet结果集合中数据集合到memberList中
			while (rs.next()) {
				LabMember member = new LabMember();
				// rs.get里面是表中字段
				member.setMemberId(rs.getInt("member_id"));
				member.setCno(rs.getInt("cno"));
				member.setStname(rs.getString("stu_name"));
				member.setStid(rs.getString("stu_id"));
				member.setSex(rs.getString("sex"));
				member.setGrade(rs.getString("grade"));
				member.setNatplace(rs.getString("native_place"));
				member.setTelephone(rs.getString("telephone"));
				member.setEmail(rs.getString("email"));
				member.setCampuspo(rs.getString("campus_post"));
				member.setJoindate(rs.getString("joining_date"));
				member.setStatus(rs.getString("status"));
				member.setLabjob(rs.getString("lab_job"));
				member.setRemark(rs.getString("remark"));
				members.add(member);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(connection, null, rs);
		}
		return members;
	}

	@Override
	public int updateLabMember(LabMember labMember) {
		Connection conn = DbUtil.getConnection();
		PreparedStatement pstmt = null;
		int count = 0;
		
		String sql = "UPDATE t_labmember SET telephone=?,campus_post=?,status=?,remark=? WHERE stu_id=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, labMember.getTelephone());
			pstmt.setString(2, labMember.getCampuspo());
			pstmt.setString(3, labMember.getStatus());
			pstmt.setString(4, labMember.getRemark());
			pstmt.setString(5, labMember.getStid());
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(conn, pstmt, null);
		}
		return count;
	}

	@Override
	public boolean insertLabMember(LabMember labmember) {
		boolean flag = false ;
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn = DbUtil.getConnection();
		
		String sql ="INSERT INTO t_labmember VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, labmember.getMemberId());
			pstmt.setInt(2, labmember.getCno());
			pstmt.setString(3, labmember.getStname());
			pstmt.setString(4, labmember.getStid());
			pstmt.setString(5, labmember.getSex());
			pstmt.setString(6, labmember.getGrade());
			pstmt.setString(7, labmember.getNatplace());
			pstmt.setString(8, labmember.getTelephone());
			pstmt.setString(9, labmember.getEmail());
			pstmt.setString(10, labmember.getCampuspo());
			pstmt.setString(11, labmember.getJoindate());
			pstmt.setString(12, labmember.getStatus());
			pstmt.setString(13, labmember.getLabjob());
			pstmt.setString(14, labmember.getRemark());
			
			int count = pstmt.executeUpdate();
			if(count != 0) {
				//添加成功
				flag = true;
			}else {
				flag = false; 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(conn, pstmt, null);
		}
		return flag;
	}

}
